﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CodeArt.SharePoint.Library
{
    using System.Data.Common;
    using System.Data;
    using System.Configuration;
    using CodeArt.SharePoint.DataProvider;
    using CodeArt.SharePoint.Configuration;

    internal class PickerDataService
    {
        private PickerControlElement _pickerTemplate = null;
        private string _connString = string.Empty;

        protected string ConnectionString
        {
            get { return _connString; }
            set { _connString = value; }
        }

        public PickerDataService(PickerControlElement pickerTemplate)
        {
            _pickerTemplate = pickerTemplate;
            this.ConnectionString =
                ConfigurationManager.ConnectionStrings[pickerTemplate.Query.ConnectionName].ConnectionString;
        }

        private DataTable ExecuteSQL(string sql)
        {
            DataTable data = null;

            try
            {
                IDbProvider provider = new SqlProvider(ConnectionString);
                DataSet dataSet = provider.ExecuteDataSet(sql);
                if (dataSet != null && dataSet.Tables.Count > 0)
                    data = dataSet.Tables[0];
            }
            catch
            {
                throw;
            }
            return data;
        }

        private string GetRowLimitFilter()
        { 
         if(_pickerTemplate.Query.RowLimit>0)
            {
                return string.Format("top {0}", _pickerTemplate.Query.RowLimit);
            }
            return string.Empty;
        }


        /// <summary>
        /// Query
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public DataTable Query(string filter)
        { 
            string sql = _pickerTemplate.Query.Value;

            sql = sql.Replace("{top}", GetRowLimitFilter());

            if (string.IsNullOrEmpty(filter))
                sql = sql.Replace("{filter}", string.Empty);
            else
                sql = sql.Replace("{filter}", " and ( " + filter + " ) ");

            return this.ExecuteSQL(sql);

        }

        /// <summary>
        /// FindRows
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public IEnumerable<DataRow> FindRows(string filter)
        {
            DataTable table = this.Query(filter);

            var selececRows = from r in table.AsEnumerable()
                              select r;
            return selececRows; 
        }

        public DataTable ConvertToTable(IEnumerable<DataRow> rows)
        {
            DataTable table = null;// CreateDataTable();
            //foreach (DataRow row in rows)
            //{
            //    table.Rows.Add(row);
            //}
            table = rows.CopyToDataTable<DataRow>();

            return table;
        }

    }
}
